package com.github.carter659.spring04;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author  yangyangh
 */
@Repository
public class TopicRespository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<Map<String,Object>> findAll(int size, int pageNum) {

        StringBuilder builder = new StringBuilder(  );
        builder.append("select count(1) as num from nlp_topic");

        SqlRowSet countResult = jdbcTemplate.queryForRowSet(builder.toString().toLowerCase(), new Object[] {});
        int count = 0 ;
        while (countResult.next()) {
          count = countResult.getInt("num");
        }
        builder = new StringBuilder(  );
        builder.append("select nlp_topic.* from nlp_topic  order by hot_score desc limit ");

        builder.append(size*pageNum + "," + size);

        SqlRowSet rows = jdbcTemplate.queryForRowSet(builder.toString().toLowerCase(), new Object[] {});
        Map<String, Object> map ;
        List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
        while (rows.next()) {
            map = new HashMap<String,Object >();
            map.put( "id",rows.getLong( "ID" ) );
            map.put( "topic",rows.getString( "TOPIC" ) );
            map.put( "hot_score",rows.getString( "HOT_SCORE" ) );
            builder = new StringBuilder(  );
            builder.append("SELECT tit,ID,picture,SUBSTRING_INDEX(SUBSTRING_INDEX(TOPICS, ' ', 1),'_',1) TOPIC,\n" +
                    "SUBSTRING_INDEX(SUBSTRING_INDEX(TOPICS, ' ', 1),'_',-1) SCORE \n" +
                    "FROM nlp_article \n" +
                    "WHERE TOPICS LIKE \"%"+rows.getString( "TOPIC" )+"%\" ORDER BY SCORE DESC limit 4 ");
            SqlRowSet news = jdbcTemplate.queryForRowSet(builder.toString().toLowerCase(), new Object[] {});
            List<Map<String,String>> lists = new ArrayList<Map<String,String>>();
            Map<String,String> article ;
            while (news.next()) {
                article = new HashMap<String,String>();
                article.put( "tit",news.getString( "TIT" ) );
                article.put( "id",news.getString( "ID" ) );
                article.put("pic",news.getString( "PICTURE" ));
                lists.add( article );
            }
            map.put( "news",lists );
            if(lists.size()>0){
                map.put( "pic",lists.get( 0 ).get( "pic" ) );
            }
            builder = new StringBuilder(  );
            builder.append("select * from nlp_topic_theme where topic  = \""+rows.getString( "TOPIC" )+" \"  order by weight desc  limit 2");
            SqlRowSet themeSet = jdbcTemplate.queryForRowSet(builder.toString().toLowerCase(), new Object[] {});
            List<String> themes = new ArrayList<String>(  );
            while (themeSet.next()){
                themes.add( themeSet.getString( "tag" ) );
            }
            map.put( "themes" ,themes);
            map.put( "num",count );
            //map.put( "hotScore",rows.getDouble(   "HOT_SCORE" ) );
            list.add( map );
        }
        return list;
    }
    public String findOne(String id) {
        StringBuilder builder = new StringBuilder(  );
        builder.append("select topic  from nlp_topic where id = "+Integer.parseInt( id ));

        SqlRowSet countResult = jdbcTemplate.queryForRowSet(builder.toString().toLowerCase(), new Object[] {});
        while (countResult.next()) {
            if(countResult.getString( "topic" )!=null){
                return countResult.getString( "topic" );
            }
        }
        return null;
    }

}
